{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据库（Database）\n",
    "\n",
    "#### 什么是数据库\n",
    "\n",
    "数据库是由许多相关数据构成的集合。\n",
    "\n",
    "当用户或者应用需要访问和使用这些数据库中的数据时，需要借助专门的管理软件系统，也就是数据库管理系统（DBMS Database Management System）。按照数据的组织和管理模式，主要的 DBMS 可以分为关系数据库管理系统和非关系数据库管理系统。\n",
    "\n",
    "\n",
    "- 关系数据库管理系统（RDBMS Relational DBMS）主要使用二维表（Table）来存储数据，类似于 Excel 中的电子表格。表中的行对应一个实体或对象，列对应实体的属性。关系数据库使用标准的结构化查询语句（SQL）执行各种数据的增删改查以及数据库的管理操作。主流的关系数据库包括 Oracle、MySQL、SQL Server 以及 PostgreSQL 等。\n",
    "\n",
    "- 非关系数据管理系统（NoSQL）通常不支持关系模型，也不提供 SQL 接口。它们通常是为了解决关系数据库在某些场景下的局限性，例如大数据、横向可扩展性等。其中，NoSQL 代表 Not Only SQL。常见的 NoSQL 数据库包括文档数据库（MongoDB 等）、键值存储（Redis 等）、以及图数据库（Neo4j 等）。\n",
    "\n",
    "\n",
    "MySQL 是最流行的开源关系数据库管理系统，由 Oracle 公司进行开发并提供支持，提供了原生的文档数据库（JSON）支持。官网地址： https://www.mysql.com/\n",
    "\n",
    "MySQL 支持各种平台，包括 Windows、Linux 以及 macOS。相对于其他大型的数据库系统（Oracle、SQL Server 等）而言，MySQL 更加容易管理和使用，同时又具有非常好的性能、可靠性和扩展性。\n",
    "\n",
    "\n",
    "SQL 代表结构化查询语言（Structured Query Language），它是管理和访问关系数据库的标准语言。通过 SQL 可以执行数据的增加（Create）、删除（Delete）、修改（Update）以及查询（Retrieve），同时还可以执行许多数据库的管理操作。\n",
    "\n",
    ">A SQL query is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.\n",
    "\n",
    "`SELECT * FROM EMPLOYEE WHERE Age > 40 OR Salary > 100000`\n",
    "\n",
    "If in case MySQL is not available, you might use MariaDB instead. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 学习 MYSQL 数据库，建议方法：\n",
    "\n",
    "1. 找几本权威的数据库图书系统地学习一下，当然可以选择你感兴趣的部分，常用的数据库知识并不多；\n",
    "2. 自己安装一个数据库进行操作、实践。\n",
    "\n",
    "\n",
    "建议你可以在阿里云上尝试注册并且使用MySQL\n",
    "https://www.aliyun.com/product/rds/mysql\n",
    "\n",
    "网上资料\n",
    "\n",
    "MySQL指南\n",
    "1. 菜鸟教程 https://www.runoob.com/mysql/mysql-tutorial.html\n",
    "2. 知乎 https://www.zhihu.com/column/mysql-tutorial\n",
    "3. MySQL数据库学习宝典 http://c.biancheng.net/mysql/\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Download\n",
    "\n",
    "MySQL  USTC 中科大的镜像 [点击前往](http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/)\n",
    "\n",
    "注意根据你的操作系统选择合适的镜像文件 ！\n",
    "\n",
    "MariaDB 开源软件 可以到其官网下载 \n",
    "\n",
    "https://downloads.mariadb.org/\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 安装MariaDB/MySQL\n",
    "\n",
    "\n",
    "Windows10系统下安装MariaDB 的教程图解 [Click](https://pcedu.pconline.com.cn/1367/13676123.html)\n",
    "\n",
    "Windows10 MYSQL Installer 安装 [Click](https://www.runoob.com/w3cnote/windows10-mysql-installer.html)\n",
    "\n",
    "Mac中MariaDB数据库的安装步骤 [Click](http://ddrfans.com/Html/1/209439.html)\n",
    "\n",
    "MacOS 安装 MariaDB [Click](https://blog.csdn.net/m0_58019259/article/details/127512318)\n",
    "\n",
    "注意：在安装过程中要设置root用户的密码，请记住并且妥善保管这个密码，在后续查看及调用数据库的时候都需要使用这个重要的密码。而且root用户是具有最高权限的，不能泄露该密码。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 如果需要修改密码，可以根据以下的方法 -- 尽量不要使用\n",
    "\n",
    "Enter password：***  \n",
    "\n",
    "mysql>use mysql;   \n",
    "\n",
    "Database changed   \n",
    "\n",
    "mysql> update user set password=password(\"newPassword\") where user='root';  将root用户的密码修改为新的密码\n",
    "\n",
    "mysql> flush privileges; 刷新权限  \n",
    "\n",
    "mysql> quit; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Navicat\n",
    "\n",
    "Powerful database management & design GUI \n",
    "\n",
    "是一套多连接数据库开发工具，让你在单一应用程序中同时连接多达七种数据库：MySQL、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL，可一次快速方便地访问所有数据库。\n",
    "\n",
    "在使用之前，先搞清楚什么是connection， database， table， column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (3564737715.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Cell \u001b[0;32mIn [2], line 1\u001b[0;36m\u001b[0m\n\u001b[0;31m    pip install mysql-connector -i http://pypi.douban.com/simple --trusted-host pypi.douban.com\u001b[0m\n\u001b[0m        ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# pip install mysql-connector -i http://pypi.douban.com/simple --trusted-host pypi.douban.com\n",
    "# pip install sqlalchemy\n",
    "# pip install pymysql\n",
    "\n",
    "# 加速器 -i http://pypi.douban.com/simple --trusted-host pypi.douban.com"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "连接数据库\n",
    "\n",
    "使用SQL语句和相应的Python工具包： sqlalchemy， mysql.connector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(99,\n",
       " 50628,\n",
       " 'live:finance-152-3712341604298851:0',\n",
       " 'yujie7@staff.sina.com.cn',\n",
       " '英国4月零售物价指数年率+2.5%，预期+2.6%，前值+2.5%；月率+0.4%，预期+0.5%，前值+0.2%。',\n",
       " '2014-05-20 16:31:13',\n",
       " 152,\n",
       " '其他')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 传统的方案，不建议大家使用\n",
    "import getpass\n",
    "\n",
    "import mysql.connector\n",
    "\n",
    "# pip install mysql-connector\n",
    "pw = getpass.getpass('Please provide the password:')\n",
    "conn = mysql.connector.connect(host= '10.23.0.3',port=3306,user=\"root\", password = pw, database = 'news_online', charset=\"utf8\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "# 使用\n",
    "sql = \"select * from sina_fin_news limit 5\"\n",
    "cur.execute(sql)\n",
    "result = cur.fetchall()\n",
    "result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['英国4月零售物价指数年率+2.5%，预期+2.6%，前值+2.5%；月率+0.4%，预期+0.5%，前值+0.2%。',\n",
       " '英国4月核心CPI年率+2.0%，创2013年9月以来最大升幅，预期+1.8%，前值+1.6%；月率+0.5%，预期+0.4%，前值+0.3%。',\n",
       " '英国3月DCLG房价指数年率+8.0%，预期+9.6%，前值+9.1%。',\n",
       " '英国4月生产者输入物价指数月率-1.1%，预期-0.2%，前值-0.4%；年率-5.5%，预期-4.9%，前值-6.3%。英国4月生产者输出物价指数月率持平，预期+0.2%，前值+0.2%；年率+0.6%，预期+0.7%，前值+0.5%。     ',\n",
       " '据世界黄金协会最新黄金需求趋势报告，一季度金条金币需求同比重挫39%至283吨，为四年来最低水平。']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res = [i[4] for i in result]\n",
    "res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import getpass\n",
    "# pip install sqlalchemy\n",
    "\n",
    "pw = getpass.getpass('Please provide the password:')\n",
    "# 127.0.0.1 代表本机，是特指本机的IP地址，3306是数据库使用的端口号，utf8是编码，一般有中文就应该使用utf8.\n",
    "# engine = create_engine('mysql+pymysql://root:{}@127.0.0.1:3306/{}?charset=utf8'.format(pw,db))\n",
    "\n",
    "engine = create_engine('mysql+pymysql://root:{}@10.23.0.3:3306/news_online?charset=utf8'.format(pw))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SELECT 语句用于从表中选取数据。 结果被存储在一个结果表中（称为结果集）。\n",
    "\n",
    "`SELECT * FROM table`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>id</th>\n",
       "      <th>commentid</th>\n",
       "      <th>creator</th>\n",
       "      <th>rich_text</th>\n",
       "      <th>update_time</th>\n",
       "      <th>zhibo_id</th>\n",
       "      <th>tag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>99</td>\n",
       "      <td>50628</td>\n",
       "      <td>live:finance-152-3712341604298851:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>英国4月零售物价指数年率+2.5%，预期+2.6%，前值+2.5%；月率+0.4%，预期+0...</td>\n",
       "      <td>2014-05-20 16:31:13</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>98</td>\n",
       "      <td>50629</td>\n",
       "      <td>live:finance-152-3712341809583957:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>英国4月核心CPI年率+2.0%，创2013年9月以来最大升幅，预期+1.8%，前值+1.6...</td>\n",
       "      <td>2014-05-20 16:33:39</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>97</td>\n",
       "      <td>50630</td>\n",
       "      <td>live:finance-152-3712342149370022:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>英国3月DCLG房价指数年率+8.0%，预期+9.6%，前值+9.1%。</td>\n",
       "      <td>2014-05-20 16:44:49</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>96</td>\n",
       "      <td>50631</td>\n",
       "      <td>live:finance-152-3712343303256165:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>英国4月生产者输入物价指数月率-1.1%，预期-0.2%，前值-0.4%；年率-5.5%，预...</td>\n",
       "      <td>2014-05-20 16:37:58</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>95</td>\n",
       "      <td>50632</td>\n",
       "      <td>live:finance-152-3712345995869212:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>据世界黄金协会最新黄金需求趋势报告，一季度金条金币需求同比重挫39%至283吨，为四年来最低水平。</td>\n",
       "      <td>2014-05-20 16:48:40</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>94</td>\n",
       "      <td>50633</td>\n",
       "      <td>live:finance-152-3712348411891881:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>第一季德国对俄罗斯出口下滑13%，主要受到汽车及机械出口大减的拖累，因乌克兰危机导致卢布贬值...</td>\n",
       "      <td>2014-05-20 16:58:17</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>93</td>\n",
       "      <td>50634</td>\n",
       "      <td>live:finance-152-3712349191890993:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>中国电信4月运营数据显示，当月移动用户再度流失103万，总数降至1.8217亿户，2014年...</td>\n",
       "      <td>2014-05-20 17:01:22</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>50635</td>\n",
       "      <td>live:finance-152-3712350483616588:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>祝宝良：从目前PMI等诸多经济指标来看，二季度经济增速会和一季度差不多，加之4月份以来国家采...</td>\n",
       "      <td>2014-05-20 17:06:30</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>91</td>\n",
       "      <td>50636</td>\n",
       "      <td>live:finance-152-3712353570754284:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>发改委：中国将加快健全若干个区域性煤炭交易市场，逐步培育建成2至3个全国性煤炭交易市场，形成...</td>\n",
       "      <td>2014-05-20 17:18:46</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>90</td>\n",
       "      <td>50637</td>\n",
       "      <td>live:finance-152-3712355676351793:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>社科院日本经济蓝皮书课题组20日预测称，参照日本经验，可以判断：在中国目前的经济高速增长阶段...</td>\n",
       "      <td>2014-05-20 17:27:09</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>89</td>\n",
       "      <td>50638</td>\n",
       "      <td>live:finance-152-3712358172056943:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>5月20日下午，深圳市市场监督管理局来到快播深圳总部，送达了对深圳市快播科技有限公司拟行政处...</td>\n",
       "      <td>2014-05-20 17:37:03</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>88</td>\n",
       "      <td>50639</td>\n",
       "      <td>live:finance-152-3712359577082710:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>中俄签署联合声明，提出建立全面的中俄能源合作伙伴关系，进一步深化石油领域一揽子合作，尽快启动...</td>\n",
       "      <td>2014-05-20 17:42:38</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>87</td>\n",
       "      <td>50640</td>\n",
       "      <td>live:finance-152-3712361111957540:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>欧盟委员会指控摩根大通、汇丰银行、法国农业信贷银行操控汇率。</td>\n",
       "      <td>2014-05-20 17:48:44</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>86</td>\n",
       "      <td>50641</td>\n",
       "      <td>live:finance-152-3712361896142336:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>汇丰控股遭欧盟指控操控汇率，股价在伦敦扩大跌幅至1.2%。</td>\n",
       "      <td>2014-05-20 17:51:51</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>85</td>\n",
       "      <td>50642</td>\n",
       "      <td>live:finance-152-3712364496717244:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>俄罗斯莫斯科以南80公里发生两列火车相撞事故，已造成至少4人丧生，15人受伤。</td>\n",
       "      <td>2014-05-20 18:02:11</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>84</td>\n",
       "      <td>50643</td>\n",
       "      <td>live:finance-152-3712364798988200:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>家得宝第一季度每股收益1.00美元，预估99美分。</td>\n",
       "      <td>2014-05-20 18:49:16</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>83</td>\n",
       "      <td>50644</td>\n",
       "      <td>live:finance-152-3712366400995963:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>知情人士表示，欧央行将考虑每六周举行一次议息会议，而非每月一次。</td>\n",
       "      <td>2014-05-20 18:09:45</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>82</td>\n",
       "      <td>50645</td>\n",
       "      <td>live:finance-152-3712366606290538:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>欧盟表示，将继续对LIBOR案件中的券商进行调查。</td>\n",
       "      <td>2014-05-20 18:10:34</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>81</td>\n",
       "      <td>50646</td>\n",
       "      <td>live:finance-152-3712368267248748:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>OECD：OECD成员国一季度GDP季率+0.4%，前值+0.5%。欧元区第一季度GDP季率...</td>\n",
       "      <td>2014-05-20 18:17:10</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>80</td>\n",
       "      <td>50647</td>\n",
       "      <td>live:finance-152-3712370649976830:0</td>\n",
       "      <td>yujie7@staff.sina.com.cn</td>\n",
       "      <td>5月20日晚间证监会再发第二十二批7家预披露名单。其中，3家在上交所上市，3家在中小板上市，...</td>\n",
       "      <td>2014-05-20 18:26:38</td>\n",
       "      <td>152</td>\n",
       "      <td>其他</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    index     id                            commentid  \\\n",
       "0      99  50628  live:finance-152-3712341604298851:0   \n",
       "1      98  50629  live:finance-152-3712341809583957:0   \n",
       "2      97  50630  live:finance-152-3712342149370022:0   \n",
       "3      96  50631  live:finance-152-3712343303256165:0   \n",
       "4      95  50632  live:finance-152-3712345995869212:0   \n",
       "5      94  50633  live:finance-152-3712348411891881:0   \n",
       "6      93  50634  live:finance-152-3712349191890993:0   \n",
       "7      92  50635  live:finance-152-3712350483616588:0   \n",
       "8      91  50636  live:finance-152-3712353570754284:0   \n",
       "9      90  50637  live:finance-152-3712355676351793:0   \n",
       "10     89  50638  live:finance-152-3712358172056943:0   \n",
       "11     88  50639  live:finance-152-3712359577082710:0   \n",
       "12     87  50640  live:finance-152-3712361111957540:0   \n",
       "13     86  50641  live:finance-152-3712361896142336:0   \n",
       "14     85  50642  live:finance-152-3712364496717244:0   \n",
       "15     84  50643  live:finance-152-3712364798988200:0   \n",
       "16     83  50644  live:finance-152-3712366400995963:0   \n",
       "17     82  50645  live:finance-152-3712366606290538:0   \n",
       "18     81  50646  live:finance-152-3712368267248748:0   \n",
       "19     80  50647  live:finance-152-3712370649976830:0   \n",
       "\n",
       "                     creator  \\\n",
       "0   yujie7@staff.sina.com.cn   \n",
       "1   yujie7@staff.sina.com.cn   \n",
       "2   yujie7@staff.sina.com.cn   \n",
       "3   yujie7@staff.sina.com.cn   \n",
       "4   yujie7@staff.sina.com.cn   \n",
       "5   yujie7@staff.sina.com.cn   \n",
       "6   yujie7@staff.sina.com.cn   \n",
       "7   yujie7@staff.sina.com.cn   \n",
       "8   yujie7@staff.sina.com.cn   \n",
       "9   yujie7@staff.sina.com.cn   \n",
       "10  yujie7@staff.sina.com.cn   \n",
       "11  yujie7@staff.sina.com.cn   \n",
       "12  yujie7@staff.sina.com.cn   \n",
       "13  yujie7@staff.sina.com.cn   \n",
       "14  yujie7@staff.sina.com.cn   \n",
       "15  yujie7@staff.sina.com.cn   \n",
       "16  yujie7@staff.sina.com.cn   \n",
       "17  yujie7@staff.sina.com.cn   \n",
       "18  yujie7@staff.sina.com.cn   \n",
       "19  yujie7@staff.sina.com.cn   \n",
       "\n",
       "                                            rich_text          update_time  \\\n",
       "0   英国4月零售物价指数年率+2.5%，预期+2.6%，前值+2.5%；月率+0.4%，预期+0...  2014-05-20 16:31:13   \n",
       "1   英国4月核心CPI年率+2.0%，创2013年9月以来最大升幅，预期+1.8%，前值+1.6...  2014-05-20 16:33:39   \n",
       "2                英国3月DCLG房价指数年率+8.0%，预期+9.6%，前值+9.1%。  2014-05-20 16:44:49   \n",
       "3   英国4月生产者输入物价指数月率-1.1%，预期-0.2%，前值-0.4%；年率-5.5%，预...  2014-05-20 16:37:58   \n",
       "4   据世界黄金协会最新黄金需求趋势报告，一季度金条金币需求同比重挫39%至283吨，为四年来最低水平。  2014-05-20 16:48:40   \n",
       "5   第一季德国对俄罗斯出口下滑13%，主要受到汽车及机械出口大减的拖累，因乌克兰危机导致卢布贬值...  2014-05-20 16:58:17   \n",
       "6   中国电信4月运营数据显示，当月移动用户再度流失103万，总数降至1.8217亿户，2014年...  2014-05-20 17:01:22   \n",
       "7   祝宝良：从目前PMI等诸多经济指标来看，二季度经济增速会和一季度差不多，加之4月份以来国家采...  2014-05-20 17:06:30   \n",
       "8   发改委：中国将加快健全若干个区域性煤炭交易市场，逐步培育建成2至3个全国性煤炭交易市场，形成...  2014-05-20 17:18:46   \n",
       "9   社科院日本经济蓝皮书课题组20日预测称，参照日本经验，可以判断：在中国目前的经济高速增长阶段...  2014-05-20 17:27:09   \n",
       "10  5月20日下午，深圳市市场监督管理局来到快播深圳总部，送达了对深圳市快播科技有限公司拟行政处...  2014-05-20 17:37:03   \n",
       "11  中俄签署联合声明，提出建立全面的中俄能源合作伙伴关系，进一步深化石油领域一揽子合作，尽快启动...  2014-05-20 17:42:38   \n",
       "12                    欧盟委员会指控摩根大通、汇丰银行、法国农业信贷银行操控汇率。   2014-05-20 17:48:44   \n",
       "13                      汇丰控股遭欧盟指控操控汇率，股价在伦敦扩大跌幅至1.2%。  2014-05-20 17:51:51   \n",
       "14            俄罗斯莫斯科以南80公里发生两列火车相撞事故，已造成至少4人丧生，15人受伤。  2014-05-20 18:02:11   \n",
       "15                          家得宝第一季度每股收益1.00美元，预估99美分。  2014-05-20 18:49:16   \n",
       "16                   知情人士表示，欧央行将考虑每六周举行一次议息会议，而非每月一次。  2014-05-20 18:09:45   \n",
       "17                          欧盟表示，将继续对LIBOR案件中的券商进行调查。  2014-05-20 18:10:34   \n",
       "18  OECD：OECD成员国一季度GDP季率+0.4%，前值+0.5%。欧元区第一季度GDP季率...  2014-05-20 18:17:10   \n",
       "19  5月20日晚间证监会再发第二十二批7家预披露名单。其中，3家在上交所上市，3家在中小板上市，...  2014-05-20 18:26:38   \n",
       "\n",
       "    zhibo_id tag  \n",
       "0        152  其他  \n",
       "1        152  其他  \n",
       "2        152  其他  \n",
       "3        152  其他  \n",
       "4        152  其他  \n",
       "5        152  其他  \n",
       "6        152  其他  \n",
       "7        152  其他  \n",
       "8        152  其他  \n",
       "9        152  其他  \n",
       "10       152  其他  \n",
       "11       152  其他  \n",
       "12       152  其他  \n",
       "13       152  其他  \n",
       "14       152  其他  \n",
       "15       152  其他  \n",
       "16       152  其他  \n",
       "17       152  其他  \n",
       "18       152  其他  \n",
       "19       152  其他  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = 'select * from sina_fin_news limit 20'\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL WHERE 子句\n",
    "\n",
    "WHERE 子句用于提取那些满足指定条件的记录。\n",
    "\n",
    "`SELECT * FROM table WHERE column_name operateor value`\n",
    "\n",
    "下面的运算符可以在 WHERE 子句中使用： =, <>, >, <, >=, <=, between and (在某个范围内), in (指定针对某个列的多个可能值), like "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                           rich_text\n",
      "0  美联储官员Todd Vermilyea周二称，美国各大银行尚未完全执行监管机构制定的“垃圾级...\n",
      "1  美联储普罗索：美国经济正处于复苏开始以来最坚实的位置，但第一季度经济走软可能加大今年实现3%...\n",
      "2    费城联储行长普罗索：至今年年底前失业率有可能降至6%以下，美联储最好在条件允许下尽早开始加息。\n",
      "3  美联储FOMC副主席、纽约联储行长达德利(William Dudley)：联储需要花一些时间...\n",
      "4  美联储达德利：开始加息后，加息的轨迹“可能相对缓慢”，这既依赖于届时经济的表现如何，也取决于...\n",
      "5  前以色列央行行长、伯南克的老师斯坦利-费希尔(Stanley Fischer)出任美联储副主...\n",
      "6  美联储达德利预计，较长期内的利率水平将“明显低于”4.25%的历史平均水平，至于联储资产负债...\n",
      "7  达德利：在削减4万亿美元资产的问题上，直接出售抵押贷款支持证券(MBS)已不在美联储的考虑之...\n",
      "8                 费城联储主席普罗瑟称，美联储的加息行动与经济状况挂钩，不与日历挂钩。\n",
      "9  周二美国股市收跌。史泰博等零售商盈利不及预期股价下跌。费城联储主席普罗瑟希望美联储在条件允许...\n"
     ]
    }
   ],
   "source": [
    "sql_cmd = 'select rich_text from sina_fin_news where rich_text like \"%%美联储%%\" limit 10'\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import getpass\n",
    "pw = getpass.getpass('Please provide the password:')\n",
    "engine = create_engine('mysql+pymysql://root:{}@10.23.0.3:3306/quant?charset=utf8'.format(pw))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>ChgAmount</th>\n",
       "      <th>ChgRate</th>\n",
       "      <th>Volume</th>\n",
       "      <th>VolTrans</th>\n",
       "      <th>Swing</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>AdjFactor</th>\n",
       "      <th>MktValue</th>\n",
       "      <th>Code</th>\n",
       "      <th>IntCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1992-01-07</td>\n",
       "      <td>12.50</td>\n",
       "      <td>12.75</td>\n",
       "      <td>12.47</td>\n",
       "      <td>12.75</td>\n",
       "      <td>0.25</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7295.0</td>\n",
       "      <td>930.0</td>\n",
       "      <td>2.24</td>\n",
       "      <td>1.08</td>\n",
       "      <td>1.617220</td>\n",
       "      <td>9.813740e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1992-09-11</td>\n",
       "      <td>25.00</td>\n",
       "      <td>25.55</td>\n",
       "      <td>24.50</td>\n",
       "      <td>25.55</td>\n",
       "      <td>0.50</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12230.0</td>\n",
       "      <td>3125.0</td>\n",
       "      <td>4.19</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1.867180</td>\n",
       "      <td>2.359916e+09</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003-12-04</td>\n",
       "      <td>6.00</td>\n",
       "      <td>6.12</td>\n",
       "      <td>5.94</td>\n",
       "      <td>6.12</td>\n",
       "      <td>0.12</td>\n",
       "      <td>2.0</td>\n",
       "      <td>87320.0</td>\n",
       "      <td>5273.0</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0.94</td>\n",
       "      <td>18.965994</td>\n",
       "      <td>8.536160e+09</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2006-08-08</td>\n",
       "      <td>5.52</td>\n",
       "      <td>5.62</td>\n",
       "      <td>5.50</td>\n",
       "      <td>5.61</td>\n",
       "      <td>0.11</td>\n",
       "      <td>2.0</td>\n",
       "      <td>154814.0</td>\n",
       "      <td>8613.0</td>\n",
       "      <td>2.18</td>\n",
       "      <td>0.53</td>\n",
       "      <td>45.365734</td>\n",
       "      <td>2.227113e+10</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008-11-20</td>\n",
       "      <td>6.30</td>\n",
       "      <td>6.79</td>\n",
       "      <td>6.25</td>\n",
       "      <td>6.63</td>\n",
       "      <td>0.13</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4112188.0</td>\n",
       "      <td>270993.0</td>\n",
       "      <td>8.31</td>\n",
       "      <td>4.37</td>\n",
       "      <td>110.285853</td>\n",
       "      <td>7.289824e+10</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>1997-07-11</td>\n",
       "      <td>9.42</td>\n",
       "      <td>10.00</td>\n",
       "      <td>9.31</td>\n",
       "      <td>9.96</td>\n",
       "      <td>0.29</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9275.0</td>\n",
       "      <td>910.0</td>\n",
       "      <td>7.14</td>\n",
       "      <td>2.05</td>\n",
       "      <td>1.813791</td>\n",
       "      <td>8.928816e+08</td>\n",
       "      <td>stock_000014</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>1997-12-05</td>\n",
       "      <td>7.50</td>\n",
       "      <td>7.90</td>\n",
       "      <td>7.50</td>\n",
       "      <td>7.65</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9704.0</td>\n",
       "      <td>754.0</td>\n",
       "      <td>5.33</td>\n",
       "      <td>2.15</td>\n",
       "      <td>1.813791</td>\n",
       "      <td>6.857976e+08</td>\n",
       "      <td>stock_000014</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>2000-11-30</td>\n",
       "      <td>19.00</td>\n",
       "      <td>19.96</td>\n",
       "      <td>18.80</td>\n",
       "      <td>19.39</td>\n",
       "      <td>0.38</td>\n",
       "      <td>2.0</td>\n",
       "      <td>14075.0</td>\n",
       "      <td>2722.0</td>\n",
       "      <td>6.10</td>\n",
       "      <td>3.11</td>\n",
       "      <td>1.813791</td>\n",
       "      <td>1.738250e+09</td>\n",
       "      <td>stock_000014</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>2002-02-06</td>\n",
       "      <td>12.00</td>\n",
       "      <td>12.59</td>\n",
       "      <td>12.00</td>\n",
       "      <td>12.23</td>\n",
       "      <td>0.24</td>\n",
       "      <td>2.0</td>\n",
       "      <td>17451.0</td>\n",
       "      <td>2141.0</td>\n",
       "      <td>4.92</td>\n",
       "      <td>3.86</td>\n",
       "      <td>1.813791</td>\n",
       "      <td>1.096380e+09</td>\n",
       "      <td>stock_000014</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>2002-03-19</td>\n",
       "      <td>13.75</td>\n",
       "      <td>14.18</td>\n",
       "      <td>13.70</td>\n",
       "      <td>14.09</td>\n",
       "      <td>0.41</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8136.0</td>\n",
       "      <td>1140.0</td>\n",
       "      <td>3.51</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1.813791</td>\n",
       "      <td>1.263123e+09</td>\n",
       "      <td>stock_000014</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date   Open   High    Low  Close  ChgAmount  ChgRate     Volume  \\\n",
       "0  1992-01-07  12.50  12.75  12.47  12.75       0.25      2.0     7295.0   \n",
       "1  1992-09-11  25.00  25.55  24.50  25.55       0.50      2.0    12230.0   \n",
       "2  2003-12-04   6.00   6.12   5.94   6.12       0.12      2.0    87320.0   \n",
       "3  2006-08-08   5.52   5.62   5.50   5.61       0.11      2.0   154814.0   \n",
       "4  2008-11-20   6.30   6.79   6.25   6.63       0.13      2.0  4112188.0   \n",
       "..        ...    ...    ...    ...    ...        ...      ...        ...   \n",
       "95 1997-07-11   9.42  10.00   9.31   9.96       0.29      3.0     9275.0   \n",
       "96 1997-12-05   7.50   7.90   7.50   7.65       0.15      2.0     9704.0   \n",
       "97 2000-11-30  19.00  19.96  18.80  19.39       0.38      2.0    14075.0   \n",
       "98 2002-02-06  12.00  12.59  12.00  12.23       0.24      2.0    17451.0   \n",
       "99 2002-03-19  13.75  14.18  13.70  14.09       0.41      3.0     8136.0   \n",
       "\n",
       "    VolTrans  Swing  Turnover   AdjFactor      MktValue          Code  IntCode  \n",
       "0      930.0   2.24      1.08    1.617220  9.813740e+08  stock_000002        2  \n",
       "1     3125.0   4.19      1.80    1.867180  2.359916e+09  stock_000002        2  \n",
       "2     5273.0   3.00      0.94   18.965994  8.536160e+09  stock_000002        2  \n",
       "3     8613.0   2.18      0.53   45.365734  2.227113e+10  stock_000002        2  \n",
       "4   270993.0   8.31      4.37  110.285853  7.289824e+10  stock_000002        2  \n",
       "..       ...    ...       ...         ...           ...           ...      ...  \n",
       "95     910.0   7.14      2.05    1.813791  8.928816e+08  stock_000014       14  \n",
       "96     754.0   5.33      2.15    1.813791  6.857976e+08  stock_000014       14  \n",
       "97    2722.0   6.10      3.11    1.813791  1.738250e+09  stock_000014       14  \n",
       "98    2141.0   4.92      3.86    1.813791  1.096380e+09  stock_000014       14  \n",
       "99    1140.0   3.51      1.80    1.813791  1.263123e+09  stock_000014       14  \n",
       "\n",
       "[100 rows x 15 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = 'select * from stock_data where ChgRate in (2,3) limit 100'\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL AND & OR 运算符\n",
    "\n",
    "- 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。\n",
    "\n",
    "- 如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>ChgAmount</th>\n",
       "      <th>ChgRate</th>\n",
       "      <th>Volume</th>\n",
       "      <th>VolTrans</th>\n",
       "      <th>Swing</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>AdjFactor</th>\n",
       "      <th>MktValue</th>\n",
       "      <th>Code</th>\n",
       "      <th>IntCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-04-18</td>\n",
       "      <td>11.45</td>\n",
       "      <td>11.61</td>\n",
       "      <td>11.28</td>\n",
       "      <td>11.50</td>\n",
       "      <td>0.29</td>\n",
       "      <td>2.59</td>\n",
       "      <td>1475840.0</td>\n",
       "      <td>169272.0</td>\n",
       "      <td>2.94</td>\n",
       "      <td>0.87</td>\n",
       "      <td>106.308803</td>\n",
       "      <td>1.974597e+11</td>\n",
       "      <td>stock_000001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-04-24</td>\n",
       "      <td>11.63</td>\n",
       "      <td>11.94</td>\n",
       "      <td>11.58</td>\n",
       "      <td>11.86</td>\n",
       "      <td>0.29</td>\n",
       "      <td>2.51</td>\n",
       "      <td>1461100.0</td>\n",
       "      <td>172515.0</td>\n",
       "      <td>3.11</td>\n",
       "      <td>0.86</td>\n",
       "      <td>106.308803</td>\n",
       "      <td>2.036411e+11</td>\n",
       "      <td>stock_000001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1991-06-28</td>\n",
       "      <td>6.55</td>\n",
       "      <td>6.60</td>\n",
       "      <td>6.50</td>\n",
       "      <td>6.55</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2.34</td>\n",
       "      <td>1825.0</td>\n",
       "      <td>136.0</td>\n",
       "      <td>1.56</td>\n",
       "      <td>0.27</td>\n",
       "      <td>1.690907</td>\n",
       "      <td>5.041568e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1991-08-02</td>\n",
       "      <td>5.25</td>\n",
       "      <td>5.30</td>\n",
       "      <td>5.15</td>\n",
       "      <td>5.30</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2.91</td>\n",
       "      <td>720.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2.91</td>\n",
       "      <td>0.11</td>\n",
       "      <td>1.753725</td>\n",
       "      <td>4.079437e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1991-10-24</td>\n",
       "      <td>7.80</td>\n",
       "      <td>8.00</td>\n",
       "      <td>7.80</td>\n",
       "      <td>7.90</td>\n",
       "      <td>0.20</td>\n",
       "      <td>2.60</td>\n",
       "      <td>4490.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>2.60</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1.689080</td>\n",
       "      <td>6.080670e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1991-11-13</td>\n",
       "      <td>14.80</td>\n",
       "      <td>15.80</td>\n",
       "      <td>14.80</td>\n",
       "      <td>15.00</td>\n",
       "      <td>0.35</td>\n",
       "      <td>2.39</td>\n",
       "      <td>7440.0</td>\n",
       "      <td>1589.0</td>\n",
       "      <td>6.83</td>\n",
       "      <td>1.10</td>\n",
       "      <td>1.638833</td>\n",
       "      <td>1.154558e+09</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1991-12-16</td>\n",
       "      <td>11.10</td>\n",
       "      <td>11.50</td>\n",
       "      <td>11.05</td>\n",
       "      <td>11.40</td>\n",
       "      <td>0.30</td>\n",
       "      <td>2.70</td>\n",
       "      <td>2065.0</td>\n",
       "      <td>358.0</td>\n",
       "      <td>4.05</td>\n",
       "      <td>0.30</td>\n",
       "      <td>1.650496</td>\n",
       "      <td>8.774638e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1991-12-28</td>\n",
       "      <td>12.10</td>\n",
       "      <td>12.50</td>\n",
       "      <td>11.95</td>\n",
       "      <td>12.40</td>\n",
       "      <td>0.25</td>\n",
       "      <td>2.06</td>\n",
       "      <td>2925.0</td>\n",
       "      <td>363.0</td>\n",
       "      <td>4.53</td>\n",
       "      <td>0.43</td>\n",
       "      <td>1.617220</td>\n",
       "      <td>9.467373e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>12.10</td>\n",
       "      <td>12.40</td>\n",
       "      <td>12.00</td>\n",
       "      <td>12.25</td>\n",
       "      <td>0.25</td>\n",
       "      <td>2.08</td>\n",
       "      <td>3315.0</td>\n",
       "      <td>405.0</td>\n",
       "      <td>3.33</td>\n",
       "      <td>0.49</td>\n",
       "      <td>1.617220</td>\n",
       "      <td>9.428887e+08</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1992-05-22</td>\n",
       "      <td>24.35</td>\n",
       "      <td>24.80</td>\n",
       "      <td>22.95</td>\n",
       "      <td>24.70</td>\n",
       "      <td>0.50</td>\n",
       "      <td>2.07</td>\n",
       "      <td>12195.0</td>\n",
       "      <td>3012.0</td>\n",
       "      <td>7.65</td>\n",
       "      <td>1.80</td>\n",
       "      <td>1.867180</td>\n",
       "      <td>2.281406e+09</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date   Open   High    Low  Close  ChgAmount  ChgRate     Volume  \\\n",
       "0 2018-04-18  11.45  11.61  11.28  11.50       0.29     2.59  1475840.0   \n",
       "1 2018-04-24  11.63  11.94  11.58  11.86       0.29     2.51  1461100.0   \n",
       "2 1991-06-28   6.55   6.60   6.50   6.55       0.15     2.34     1825.0   \n",
       "3 1991-08-02   5.25   5.30   5.15   5.30       0.15     2.91      720.0   \n",
       "4 1991-10-24   7.80   8.00   7.80   7.90       0.20     2.60     4490.0   \n",
       "5 1991-11-13  14.80  15.80  14.80  15.00       0.35     2.39     7440.0   \n",
       "6 1991-12-16  11.10  11.50  11.05  11.40       0.30     2.70     2065.0   \n",
       "7 1991-12-28  12.10  12.50  11.95  12.40       0.25     2.06     2925.0   \n",
       "8 1992-01-31  12.10  12.40  12.00  12.25       0.25     2.08     3315.0   \n",
       "9 1992-05-22  24.35  24.80  22.95  24.70       0.50     2.07    12195.0   \n",
       "\n",
       "   VolTrans  Swing  Turnover   AdjFactor      MktValue          Code  IntCode  \n",
       "0  169272.0   2.94      0.87  106.308803  1.974597e+11  stock_000001        1  \n",
       "1  172515.0   3.11      0.86  106.308803  2.036411e+11  stock_000001        1  \n",
       "2     136.0   1.56      0.27    1.690907  5.041568e+08  stock_000002        2  \n",
       "3      46.0   2.91      0.11    1.753725  4.079437e+08  stock_000002        2  \n",
       "4     629.0   2.60      0.66    1.689080  6.080670e+08  stock_000002        2  \n",
       "5    1589.0   6.83      1.10    1.638833  1.154558e+09  stock_000002        2  \n",
       "6     358.0   4.05      0.30    1.650496  8.774638e+08  stock_000002        2  \n",
       "7     363.0   4.53      0.43    1.617220  9.467373e+08  stock_000002        2  \n",
       "8     405.0   3.33      0.49    1.617220  9.428887e+08  stock_000002        2  \n",
       "9    3012.0   7.65      1.80    1.867180  2.281406e+09  stock_000002        2  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = 'select * from stock_data where ChgRate > 2 and ChgRate < 3 limit 10'\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQL ORDER BY 关键字\n",
    "\n",
    "- ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。\n",
    "\n",
    "- ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>ChgAmount</th>\n",
       "      <th>ChgRate</th>\n",
       "      <th>Volume</th>\n",
       "      <th>VolTrans</th>\n",
       "      <th>Swing</th>\n",
       "      <th>Turnover</th>\n",
       "      <th>AdjFactor</th>\n",
       "      <th>MktValue</th>\n",
       "      <th>Code</th>\n",
       "      <th>IntCode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-01-29</td>\n",
       "      <td>12.70</td>\n",
       "      <td>13.17</td>\n",
       "      <td>12.62</td>\n",
       "      <td>13.01</td>\n",
       "      <td>0.01</td>\n",
       "      <td>0.08</td>\n",
       "      <td>1468303.0</td>\n",
       "      <td>190330.0</td>\n",
       "      <td>4.23</td>\n",
       "      <td>1.52</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.436383e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-01-28</td>\n",
       "      <td>13.11</td>\n",
       "      <td>13.27</td>\n",
       "      <td>12.88</td>\n",
       "      <td>13.00</td>\n",
       "      <td>-0.18</td>\n",
       "      <td>-1.37</td>\n",
       "      <td>1561743.0</td>\n",
       "      <td>203582.0</td>\n",
       "      <td>2.96</td>\n",
       "      <td>1.61</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.435279e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-01-14</td>\n",
       "      <td>13.07</td>\n",
       "      <td>13.30</td>\n",
       "      <td>12.87</td>\n",
       "      <td>13.10</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0.23</td>\n",
       "      <td>1646818.0</td>\n",
       "      <td>216027.0</td>\n",
       "      <td>3.29</td>\n",
       "      <td>1.70</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.442969e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-01-13</td>\n",
       "      <td>13.05</td>\n",
       "      <td>13.38</td>\n",
       "      <td>12.97</td>\n",
       "      <td>13.07</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>-0.38</td>\n",
       "      <td>1664610.0</td>\n",
       "      <td>218799.0</td>\n",
       "      <td>3.13</td>\n",
       "      <td>1.72</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.439664e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-01-30</td>\n",
       "      <td>13.09</td>\n",
       "      <td>13.49</td>\n",
       "      <td>12.80</td>\n",
       "      <td>13.12</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.85</td>\n",
       "      <td>2096247.0</td>\n",
       "      <td>275310.0</td>\n",
       "      <td>5.30</td>\n",
       "      <td>2.17</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.448528e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2015-01-16</td>\n",
       "      <td>13.77</td>\n",
       "      <td>13.77</td>\n",
       "      <td>13.39</td>\n",
       "      <td>13.52</td>\n",
       "      <td>-0.25</td>\n",
       "      <td>-1.82</td>\n",
       "      <td>2129476.0</td>\n",
       "      <td>289225.0</td>\n",
       "      <td>2.76</td>\n",
       "      <td>2.20</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.489232e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2015-01-26</td>\n",
       "      <td>13.99</td>\n",
       "      <td>14.00</td>\n",
       "      <td>13.44</td>\n",
       "      <td>13.70</td>\n",
       "      <td>-0.22</td>\n",
       "      <td>-1.58</td>\n",
       "      <td>2162685.0</td>\n",
       "      <td>295728.0</td>\n",
       "      <td>4.02</td>\n",
       "      <td>2.24</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.512564e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2015-01-15</td>\n",
       "      <td>13.13</td>\n",
       "      <td>13.83</td>\n",
       "      <td>13.00</td>\n",
       "      <td>13.77</td>\n",
       "      <td>0.67</td>\n",
       "      <td>5.11</td>\n",
       "      <td>2429687.0</td>\n",
       "      <td>326554.0</td>\n",
       "      <td>6.34</td>\n",
       "      <td>2.51</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.516769e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2015-01-12</td>\n",
       "      <td>13.32</td>\n",
       "      <td>13.32</td>\n",
       "      <td>12.75</td>\n",
       "      <td>13.12</td>\n",
       "      <td>-0.33</td>\n",
       "      <td>-2.45</td>\n",
       "      <td>2436341.0</td>\n",
       "      <td>318096.0</td>\n",
       "      <td>4.24</td>\n",
       "      <td>2.52</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.445172e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2015-01-08</td>\n",
       "      <td>14.32</td>\n",
       "      <td>14.37</td>\n",
       "      <td>13.46</td>\n",
       "      <td>13.59</td>\n",
       "      <td>-0.64</td>\n",
       "      <td>-4.50</td>\n",
       "      <td>2639394.0</td>\n",
       "      <td>362955.0</td>\n",
       "      <td>6.39</td>\n",
       "      <td>2.73</td>\n",
       "      <td>123.296083</td>\n",
       "      <td>1.496942e+11</td>\n",
       "      <td>stock_000002</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date   Open   High    Low  Close  ChgAmount  ChgRate     Volume  \\\n",
       "0 2015-01-29  12.70  13.17  12.62  13.01       0.01     0.08  1468303.0   \n",
       "1 2015-01-28  13.11  13.27  12.88  13.00      -0.18    -1.37  1561743.0   \n",
       "2 2015-01-14  13.07  13.30  12.87  13.10       0.03     0.23  1646818.0   \n",
       "3 2015-01-13  13.05  13.38  12.97  13.07      -0.05    -0.38  1664610.0   \n",
       "4 2015-01-30  13.09  13.49  12.80  13.12       0.11     0.85  2096247.0   \n",
       "5 2015-01-16  13.77  13.77  13.39  13.52      -0.25    -1.82  2129476.0   \n",
       "6 2015-01-26  13.99  14.00  13.44  13.70      -0.22    -1.58  2162685.0   \n",
       "7 2015-01-15  13.13  13.83  13.00  13.77       0.67     5.11  2429687.0   \n",
       "8 2015-01-12  13.32  13.32  12.75  13.12      -0.33    -2.45  2436341.0   \n",
       "9 2015-01-08  14.32  14.37  13.46  13.59      -0.64    -4.50  2639394.0   \n",
       "\n",
       "   VolTrans  Swing  Turnover   AdjFactor      MktValue          Code  IntCode  \n",
       "0  190330.0   4.23      1.52  123.296083  1.436383e+11  stock_000002        2  \n",
       "1  203582.0   2.96      1.61  123.296083  1.435279e+11  stock_000002        2  \n",
       "2  216027.0   3.29      1.70  123.296083  1.442969e+11  stock_000002        2  \n",
       "3  218799.0   3.13      1.72  123.296083  1.439664e+11  stock_000002        2  \n",
       "4  275310.0   5.30      2.17  123.296083  1.448528e+11  stock_000002        2  \n",
       "5  289225.0   2.76      2.20  123.296083  1.489232e+11  stock_000002        2  \n",
       "6  295728.0   4.02      2.24  123.296083  1.512564e+11  stock_000002        2  \n",
       "7  326554.0   6.34      2.51  123.296083  1.516769e+11  stock_000002        2  \n",
       "8  318096.0   4.24      2.52  123.296083  1.445172e+11  stock_000002        2  \n",
       "9  362955.0   6.39      2.73  123.296083  1.496942e+11  stock_000002        2  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = 'select * from stock_data where Code like \"%%000002\" and Date like \"2015-01%%\" ORDER BY Volume limit 10 '\n",
    "\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "586"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 通过写入数据库来创建表格\n",
    "import pandas as pd\n",
    "\n",
    "dat = pd.read_csv('stockdata.csv')\n",
    "\n",
    "dat.to_sql(name='tmp_table', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 创建表格 - 精细化的操作\n",
    "\n",
    "conn = mysql.connector.connect(host= '127.0.0.1',port=3306,user=\"root\",\\\n",
    "                               password = '123456' ,database = 'test' ,charset=\"utf8\")\n",
    "cur = conn.cursor()\n",
    "sql = \"create table if not exists stock_abc (id int not null, date varchar(20), open varchar(100))\"\n",
    "cur.execute(sql)\n",
    "\n",
    "sql =\"\"\"insert into stock_abcd (date, prices) values ('20200102', '12.3')\"\"\"\n",
    "try:\n",
    "    cur.execute(sql)\n",
    "    conn.commit()\n",
    "    \n",
    "except:\n",
    "    conn.rollback()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 删除表格\n",
    "\n",
    "sql ='DROP TABLE IF EXISTS tmp_table'\n",
    "result = engine.execute(sql)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MongoDB\n",
    "\n",
    "`pip install pymongo`\n",
    " \n",
    "或者下载相应的wheel文件，tar.gz文件进行安装 \n",
    "\n",
    "- 安装wheel 先下载文件***.whl， 然后切换到该whl文件的目录。运行 pip install filename.whl \n",
    "- 安装tar.gz 先下载文件***.tar.gz, 然后将该文件解压到相应目录。运行 pip install 解压目录  注意该目录中应该有setup.py文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'interlock')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymongo\n",
    " \n",
    "myclient = pymongo.MongoClient(\"mongodb://localhost:27017/\")\n",
    "mydb = myclient[\"interlock\"]\n",
    "\n",
    "mydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
